﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;

namespace DevCore.Utility
{
    /// <summary>
    /// Excel辅助类
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// 从Excel流文件中导入数据至DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件</param>
        /// <param name="sheetName">Excel中Sheet名称</param>
        /// <param name="headerRowIndex">从第几行开始</param>
        /// <returns></returns>
        public static DataTable ImportDataTableFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex)
        {
            String cellValue = String.Empty;
            HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
            NPOI.SS.UserModel.Sheet sheet = workbook.GetSheet(sheetName);
            DataTable table = new DataTable();
            NPOI.SS.UserModel.Row headerRow = sheet.GetRow(headerRowIndex);
            int cellCount = headerRow.LastCellNum;
            //设置datatable column的字段名称
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            //得到当前记录的第一行与第后一行的行号
            int firstRowNum = sheet.FirstRowNum;
            int lastRowNum = sheet.LastRowNum;
            for (int i = firstRowNum + 1; i <= lastRowNum; i++)
            {
                NPOI.SS.UserModel.Row row = sheet.GetRow(i);
                if (row != null)
                {
                    DataRow dataRow = table.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        cellValue = "";
                        if (row.GetCell(j) != null)
                        {
                            cellValue = row.GetCell(j).ToString();
                        }
                        dataRow[j] = cellValue;
                    }

                    table.Rows.Add(dataRow);
                }

            }
            excelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }

        /// <summary>
        /// 从Excel文件导入数据至Excel中
        /// </summary>
        /// <param name="excelFilePath"></param>
        /// <param name="sheetName"></param>
        /// <param name="headerRowIndex"></param>
        /// <returns></returns>
        public static DataTable ImportDataTableFromExcel(string excelFilePath, string sheetName, int headerRowIndex)
        {
            using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
            {
                return ImportDataTableFromExcel(stream, sheetName, headerRowIndex);
            }
        }

        /// <summary>
        /// 从第几个Excel文件流导入至数据库中
        /// </summary>
        /// <param name="excelFileStream"></param>
        /// <param name="sheetIndex"></param>
        /// <param name="headerRowIndex"></param>
        /// <returns></returns>
        public static DataTable ImportDataTableFromExcel(Stream excelFileStream, int sheetIndex, int headerRowIndex)
        {
            HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
            NPOI.SS.UserModel.Sheet sheet = workbook.GetSheetAt(sheetIndex);
            DataTable table = new DataTable();
            NPOI.SS.UserModel.Row headerRow = sheet.GetRow(headerRowIndex);
            int cellCount = headerRow.LastCellNum;
            for (
                int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
                {                // 如果遇到第一个空列，则不再继续向后读取   
                    cellCount = i + 1;
                    break;
                }
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }
            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                NPOI.SS.UserModel.Row row = sheet.GetRow(i);
                if (row == null || row.GetCell(0) == null || row.GetCell(0).ToString().Trim() == "")
                {
                    // 如果遇到第一个空行，则不再继续向后读取 
                    break;
                }
                DataRow dataRow = table.NewRow();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    dataRow[j] = row.GetCell(j);
                }
                table.Rows.Add(dataRow);
            }
            excelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }

        /// <summary>
        /// 流文件获得第几个sheet得到内容
        /// </summary>
        /// <param name="excelFilePath">文件路径</param>
        /// <param name="sheetIndex">第几个sheet</param>
        /// <param name="headerRowIndex"></param>
        /// <returns></returns>
        public static DataTable ImportDataTableFromExcel(string excelFilePath, int sheetIndex, int headerRowIndex)
        {
            using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
            {
                return ImportDataTableFromExcel(stream, sheetIndex, headerRowIndex);
            }
        }

        /// <summary>
        /// 从Excel获得数据加至DataSet
        /// </summary>
        /// <param name="excelFileStream"></param>
        /// <param name="headerRowIndex"></param>
        /// <returns></returns>
        public static DataSet ImportDataSetFromExcel(Stream excelFileStream, int headerRowIndex)
        {
            DataSet ds = new DataSet();
            HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
            for (int a = 0, b = workbook.NumberOfSheets; a < b; a++)
            {
                NPOI.SS.UserModel.Sheet sheet = workbook.GetSheetAt(a);
                DataTable table = new DataTable();
                NPOI.SS.UserModel.Row headerRow = sheet.GetRow(headerRowIndex);
                int cellCount = headerRow.LastCellNum;
                for (
                    int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
                    {
                        // 如果遇到第一个空列，则不再继续向后读取   
                        cellCount = i + 1;
                        break;
                    }
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }
                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    NPOI.SS.UserModel.Row row = sheet.GetRow(i);
                    if (row == null || row.GetCell(0) == null || row.GetCell(0).ToString().Trim() == "")
                    {
                        // 如果遇到第一个空行，则不再继续向后读取   
                        break;
                    }
                    DataRow dataRow = table.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                    }
                    table.Rows.Add(dataRow);
                }
                ds.Tables.Add(table);
            }
            excelFileStream.Close();
            workbook = null;
            return ds;
        }

        /// <summary>
        /// 从Excel文件加载至DataSet
        /// </summary>
        /// <param name="excelFilePath"></param>
        /// <param name="headerRowIndex"></param>
        /// <returns></returns>
        public static DataSet ImportDataSetFromExcel(string excelFilePath, int headerRowIndex)
        {
            using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
            {
                return ImportDataSetFromExcel(stream, headerRowIndex);
            }
        }
    }
}